<?php
include_once '../inc/config.inc.php';
include_once '../inc/db_config.inc.php';

// connect to mysql server
$conn = mysql_connect($db_host, $db_user, $db_password) or die("Could not connect to mysql server: " . mysql_error()); 

// connect to the database
mysql_select_db($db_name) or die("Database: database not found"); 

function getFixtures($sql, $conn) {
    $result = mysql_query($sql, $conn) or die(mysql_error());
    return $result;
}

function printFixtures($result) {
    $num_rows = mysql_num_rows($result);
    $response = '';
    
    $response .= '[';
    $i = 0;
    while($row = mysql_fetch_assoc($result)){
        $response .= '{';
        $response .= '"game_id":"' . $row['game_id'].'",';
        $response .= '"kickoff":"' . $row['kickoff'] . '",';
        $response .= '"team_a":"' . $row['team_a'] . '",';
        $response .= '"team_a_cc":"' . $row['team_a_cc'] . '",';
        $response .= '"team_b":"' . $row['team_b'] . '",';
        $response .= '"team_b_cc":"' . $row['team_b_cc'] . '",';
        $response .= '"details":"' . $row['details'] . '",';
        $response .= '"city":"' . $row['city'] . '",';
        $response .= '"venue":"' . $row['venue'] . '",';
        $response .= '"stage":"' . $row['stage'] . '",';
        $response .= '"goals_a":"' . $row['goals_a'] . '",';
        $response .= '"goals_b":"' . $row['goals_b'] . '"';
        $response .= '}';
        
        if ($i < $num_rows-1) {
            $response .= ',';   
        }
        $i++;
    }
    $response .= ']';
    
    echo $response;
}

if (isset($_GET['lang']) && !empty($_GET['lang']) && strlen($_GET['lang']) == 2) {
    $lang = $_GET['lang'];
} else {
    $lang = "en";
}

if (isset($_GET['cc']) && !empty($_GET['cc']) && strlen($_GET['cc']) == 2) {
    $cc = $_GET['cc'];
    
    // get fixtures by country code
    $sql = "SELECT f.game_id, f.kickoff, city.name_$lang as city, team_a.name_$lang as team_a,
    team_a.cc as team_a_cc, team_b.name_$lang as team_b, team_b.cc as team_b_cc, f.details,
    venue.name as venue, stage.name_$lang as stage, f.goals_a, f.goals_b
    FROM eu_fixtures as f, city, team as team_a, stage, venue, team as team_b
    WHERE
        f.team_a_id = team_a.id AND
        f.team_b_id = team_b.id AND
        f.city_id = city.id AND
        f.venue_id = venue.id AND
        f.stage_id = stage.id AND
        (team_a.cc = '$cc' OR team_b.cc = '$cc')";
}

if (isset($_GET['country_norm']) && !empty($_GET['country_norm']) && strlen($_GET['country_norm']) < 15) {
    $country_norm = $_GET['country_norm'];
    
    // get fixtures by country code
    $sql = "SELECT f.game_id, f.kickoff, city.name_$lang as city, team_a.name_$lang as team_a,
    team_a.cc as team_a_cc, team_b.name_$lang as team_b, team_b.cc as team_b_cc, f.details,
    venue.name as venue, stage.name_$lang as stage, f.goals_a, f.goals_b
    FROM eu_fixtures as f, city, team as team_a, stage, venue, team as team_b
    WHERE
        f.team_a_id = team_a.id AND
        f.team_b_id = team_b.id AND
        f.city_id = city.id AND
        f.venue_id = venue.id AND
        f.stage_id = stage.id AND
        (team_a.slug = '$country_norm' OR team_b.slug = '$country_norm')";
}

if (isset($_GET['stageId']) && !empty($_GET['stageId']) && strlen($_GET['stageId']) <= 15 ) {
    $stageId = stripslashes(urldecode(strip_tags($_GET['stageId'])));
    
    // get fixtures by stage    
    $sql = "SELECT f.game_id, f.kickoff, city.name_$lang as city, team_a.name_$lang as team_a,
    team_a.cc as team_a_cc, team_b.name_$lang as team_b, team_b.cc as team_b_cc, f.details,
    venue.name as venue, stage.name_$lang as stage, f.goals_a, f.goals_b
    FROM eu_fixtures as f, city, team as team_a, stage, venue, team as team_b
    WHERE
        f.team_a_id = team_a.id AND
        f.team_b_id = team_b.id AND
        f.city_id = city.id AND
        f.venue_id = venue.id AND
        f.stage_id = stage.id AND
        stage.slug = '$stageId'";
}

if (isset($_GET['city']) && !empty($_GET['city']) && strlen($_GET['city']) <= 10) {
    $city = $_GET['city'];
    
    // get fixtures by city code
    $sql = "SELECT f.game_id, f.kickoff, city.name_$lang as city, team_a.name_$lang as team_a,
    team_a.cc as team_a_cc, team_b.name_$lang as team_b, team_b.cc as team_b_cc, f.details,
    venue.name as venue, stage.name_$lang as stage, f.goals_a, f.goals_b
    FROM eu_fixtures as f, city, team as team_a, stage, venue, team as team_b
    WHERE
        f.team_a_id = team_a.id AND
        f.team_b_id = team_b.id AND
        f.city_id = city.id AND
        f.venue_id = venue.id AND
        f.stage_id = stage.id AND ";
        
    if (strlen($city) > 3) {
        $sql .= "city.slug = '$city'";
    } else {
        $sql .= "city.cc = '$city'";
    }
}

if (isset($_GET['date']) && !empty($_GET['date']) && strlen($_GET['date']) == 10) {
    $date = stripslashes(urldecode(strip_tags($_GET['date'])));
    
    // get fixtures by city code
    $sql = "SELECT f.game_id, f.kickoff, city.name_$lang as city, team_a.name_$lang as team_a,
    team_a.cc as team_a_cc, team_b.name_$lang as team_b, team_b.cc as team_b_cc, f.details,
    venue.name as venue, stage.name_$lang as stage, f.goals_a, f.goals_b
    FROM eu_fixtures as f, city, team as team_a, stage, venue, team as team_b
    WHERE
        f.team_a_id = team_a.id AND
        f.team_b_id = team_b.id AND
        f.city_id = city.id AND
        f.venue_id = venue.id AND
        f.stage_id = stage.id AND
        DAYOFMONTH(TIMESTAMP(f.kickoff)) = DAYOFMONTH(TIMESTAMP('".$date."'))";
}

if ($_GET['getAllFixtures'] == 1) {
    
    //echo $lang;
    // get all fixtures
    $sql = "SELECT f.game_id, f.kickoff, city.name_$lang as city, team_a.name_$lang as team_a,
    team_a.cc as team_a_cc, team_b.name_$lang as team_b, team_b.cc as team_b_cc, f.details,
    venue.name as venue, stage.name_$lang as stage, f.goals_a, f.goals_b
    FROM eu_fixtures as f, city, team as team_a, stage, venue, team as team_b
    WHERE
        f.team_a_id = team_a.id AND
        f.team_b_id = team_b.id AND
        f.city_id = city.id AND
        f.venue_id = venue.id AND
        f.stage_id = stage.id
    ";
}

$result = getFixtures($sql, $conn);
printFixtures($result);

// free resultset
mysql_free_result($result);

// close connectio to database
mysql_close();
?>